pub struct SqlString {
    table_name: String,
    field_sql: String,
    where_sql: String,
    order_sql: String,
    join_sql: String,
    // postgresql 需要使用$ + 编号的方式显示占位符，index表示编号，从1开始，默认0
    // 这里只有where用到了
    index: i32
}

impl SqlString {
    pub fn new(table_name: &str) ->Self {
        SqlString {
            table_name: table_name.to_string(),
            field_sql: "*".to_string(),
            where_sql: "".to_string(),
            order_sql: "".to_string(),
            join_sql: "".to_string(),
            index: 0
        }
    }

    /// 连表查询字段
    pub fn join_field(mut self, fields: &Vec<String>) -> Self {
        if fields.len() == 0 {
            return self
        }
        self.field_sql = fields.join(",");
        self 
    }

     /// 字段
     pub fn field_slice(mut self, fields: &[&str])->Self {
        if fields.len() == 0 {
            return self
        }
        // 格式： id, name
        self.field_sql = fields.join(",");
        self 
    }

    
    /// 字段
    pub fn field(mut self, fields: &str)->Self {
        if fields.len() == 0 {
            return self
        }
        // 格式： id, name
        self.field_sql = fields.to_string();
        self
    }
    /* 
     // 追加字段
    pub fn append_field(mut self, fields: &str) -> Self {
        if fields.len() == 0 {
            return self
        }
        self.field_sql = format!("{},{}", self.field_sql, fields);
        self
    }
    */

    // 追加主键字段
    pub fn append_primary_key(mut self) -> Self {
        self.field_sql = format!("id,{}", self.field_sql);
        self
    }

    /// join查询
    pub fn join(mut self, for_key: &str, join_table: &str, per_key: &str)->Self {
        // 格式： left  join demo_user on demo.id = demo_user.demo_id
        let join_sql = format!(" LEFT  JOIN {} on {}.{} = {}.{} ", join_table, self.table_name, for_key, join_table, per_key);
        if self.join_sql.len() == 0 {
            self.join_sql = join_sql;
        } else {
            self.join_sql = format!(" {} {} ", self.join_sql, join_sql);
        }
        self 
    }

    pub fn where_x(mut self, str_key: &str, str_operate:&str) ->Self {
        if self.index == 0 {
            self.index = 1;
        } else {
            self.index += 1;
        };
        let eq_sql = format!(" {} {} ${} ", str_key, str_operate, self.index);
        if self.where_sql.len() == 0 {
            self.where_sql = eq_sql;
        } else {
            self.where_sql = format!(" {} AND {} ", self.where_sql, eq_sql);
        }
        self 
    }

     /// 条件 =
     pub fn where_primary_key(self)->Self {
        // 格式 id = $1
        self.where_x("id", "=") 
    }

    /// 条件 =
    pub fn where_eq(self, str_key: &str)->Self {
        // 格式 id = $1
        self.where_x(str_key, "=")
    }

    /// 条件 like
    pub fn where_like(self, str_key: &str)->Self {
        // 格式 name like $1
        self.where_x(str_key, "LIKE")
    }

    /// 排序
    pub fn order_x(mut self, order_by: &str, sort_by: &str)->Self {
        let order_sql = format!(" {} {} ", order_by, sort_by);
        if self.order_sql.len() == 0 {
            self.order_sql = order_sql;
        } else {
            self.order_sql = format!(" {},{} ", self.order_sql, order_by);
        }
        self 
    }

     /// 排序
     pub fn order_primary_key_desc(self)->Self {
        self.order_x("id", "DESC")
    }

    /// 排序
    pub fn order_desc(self, order_by: &str)->Self {
        self.order_x(order_by, "DESC")
    }

    
    /// 排序
    pub fn order_asc(self, order_by: &str)->Self {
        self.order_x(order_by, "ASC")
    }
     


    /// 查询
    pub fn select(&self) -> String {
        // 格式： select id, name from demo where id = $1 and name = $2 order by name, id desc
        let mut select_sql = format!("SELECT {} FROM {} ", self.field_sql, self.table_name);
        if self.join_sql.len() > 0 {
            select_sql = format!("{} {} ", select_sql, self.join_sql);
        }
        if self.where_sql.len() > 0 {
            select_sql = format!("{} WHERE {} ", select_sql, self.where_sql);
        }
        if self.order_sql.len() > 0 {
            select_sql = format!("{} ORDER BY {} ", select_sql, self.order_sql);
        }
        select_sql
    }

    /// 查询前n条数据
    pub fn limit(&self, limit: i32) -> String {
        // 格式： select id, name from demo where id = $1 and name = $2 order by name, id desc
        let select_sql = self.select();
        format!(" {} LIMIT {} ", select_sql, limit)
    }

    // 数量
    pub fn count(&self) ->String {
         // 格式： select count(1) from demo where id = $1 and name = $2 order by name, id desc
         let mut select_sql = format!("SELECT COUNT(1) FROM {} ", self.table_name);
         if self.where_sql.len() > 0 {
             select_sql = format!("{} WHERE {} ", select_sql, self.where_sql);
         }
         select_sql
    }

    /// 分页
    pub fn pagination(&self, page: i64, page_size: i64) -> String {
        let offset = (page - 1) * page_size;
        let select_sql = self.select();
        // 格式： limit 1 offset 10
        format!(" {} LIMIT {} OFFSET {} ", select_sql, page_size, offset)
    }


    /// 插入
    pub fn insert(&self) -> String {
        if self.field_sql.len() == 0 {
            return "".to_string();
        }
        let mut value_placeholder = String::from("");
        // 将field字符串分割到数组
        let field_arr = self.field_sql.split(",");
        for i in 1..field_arr.count()+1 {
            value_placeholder.push_str(format!("${},", i).as_str());
        }

        // 格式： insert into demo (id, name, age) values ($1, $2, $3)
        format!("INSERT INTO {} ({}, created_at, updated_at) VALUES({} now(), now()) RETURNING id", self.table_name, self.field_sql, value_placeholder)
    }
    
    /// 更新
    pub fn update(mut self) -> String {
        if self.field_sql.len() == 0 {
            return "".to_string();
        }
        if self.where_sql.len() == 0 {
            return "".to_string();
        }
        let mut update_data = String::from("");
        // 将field字符串分割到数组
        let field_arr = self.field_sql.split(",");
        
        if self.index == 0 {
            self.index = 1;
        } else {
            self.index += 1;
        }
        
        for field in field_arr {
            update_data.push_str(format!(" {} = ${}, ", field, self.index).as_str());
            self.index += 1;
        }

        // 格式 update demo set id = $1, name = $2, age = $3 where id = $4 and name = $5
        let mut update_sql = format!("UPDATE {} SET {} updated_at = now()", self.table_name, update_data);
        if self.where_sql.len() > 0 {
            update_sql = format!(" {} WHERE {} ", update_sql, self.where_sql);
        }
        update_sql
    }
    
    /// 删除
    pub fn delete(&self) -> String {
        if self.where_sql.len() == 0 {
            return "".to_string();
        }
        // 格式：delete from demo where id = $1 and name = $2
        format!("DELETE FROM {} WHERE {}", self.table_name, self.where_sql)
    }

}